package org.ssp.impl;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.ssp.base.ResultSetHanlder;
import org.ssp.dao.TaskDao;
import org.ssp.pojo.Assister;
import org.ssp.pojo.Task;
import org.ssp.pojo.Travel;
import org.ssp.pojo.User;
import org.ssp.util.DBUtil;



public class TaskImpl implements TaskDao {
    //1.创建任务
	@Override
	public int addTask(Task task) {
		String sql="insert into task(name,content,due,assistid,status,responsibility) values(?,?,?,?,0,?);";
		String sql2="select last_insert_id();";
		return DBUtil.update2(sql,sql2,task.getName(),task.getContent(),task.getDue(),task.getAssistid(),task.getResponsibility());
    }
/*	//3,
	@Override
	public Task findById(int id) {
		String sql="select * from task where id=?;";
		return (Task)DBUtil.query(sql, new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				Task task=new Task();
				try {
					if(rs.next()){
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setDue(rs.getDate("due"));
						task.setAssistid(rs.getString("assistid"));
						task.setParentid(rs.getInt("parentid"));
						task.setStartdate(rs.getDate("startdate"));
						task.setStatus(Integer.valueOf(rs.getInt("status")));
						task.setBackinfo(rs.getString("backinfo"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return task;
			}
			
		},id);
	}
	//4，更新任务详情
	@Override
	public void update(Task task) {
		String sql="update task set name=?,content=?,due=?,assistid=? where id=?";
		DBUtil.update(sql, task.getName(),task.getContent(),task.getDue(),task.getAssistid(),task.getId());
	}
	//5,删除任务
	@Override
	public void delete(String id) {
		String sql="delete from task where id=?;";
		DBUtil.update(sql, id);
	}*/
    //6，所有任务的列表
	@Override
	public ArrayList<Task> findAll() {
		String sql="select * from task  order by due desc, id desc;";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder(){
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
						Task task=new Task();
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setDue(rs.getDate("due"));
						task.setAssistid(rs.getString("assistid"));
						task.setParentid(rs.getInt("parentid"));
						task.setStatus(Integer.valueOf(rs.getInt("status")));
						task.setResponsibility(rs.getString("responsibility"));
						task.setAttachmenturl(rs.getString("attachmenturl"));
						list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		},null);
	}
	@Override
	public ArrayList<Task> findAllByAssist(String assistid) {
		String sql="select * from task where id in (select id from task where assistid=? or responsibility=?) or" 
                  +" id in (select id from task where id in (select taskid from task_joins where assistid=?));";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
						Task task=new Task();
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setDue(rs.getDate("due"));
						task.setAssistid(rs.getString("assistid"));
						task.setParentid(rs.getInt("parentid"));
						task.setStatus(Integer.valueOf(rs.getInt("status")));
						task.setResponsibility(rs.getString("responsibility"));
						task.setAttachmenturl(rs.getString("attachmenturl"));
						list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, assistid,assistid,assistid);
	}
    //7.所有任务计数
	/*@Override
	public int findAll() {
		String sql="select * from task;";
		return (Integer)DBUtil.query(sql,  new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				Integer total=0;
				try {
					while(rs.next()){
						total+=1;
					}
				} catch (SQLException e) {
					e.printStackTrace();
					return null;
				}
				return total;
			}
		}, null);
	}*/
/*	//8.父任务的子任务
	@Override
	public ArrayList<Task> findSub(int parentid) {
		String sql="select * from task where parentid=?";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
					   Task task=new Task();
					   task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setDue(rs.getDate("due"));
						task.setAssistid(rs.getString("assistid"));
						task.setParentid(rs.getInt("parentid"));
						task.setStartdate(rs.getDate("startdate"));
						task.setStatus(Integer.valueOf(rs.getInt("status")));
						task.setBackinfo(rs.getString("backinfo"));

						list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
			
		}, parentid);
	}
	//9，改变任务的状态
	@Override
	public void UpdateStatus(int id, int status) {
		String sql="update task set status=? where id=?;";
		DBUtil.update(sql,status,id);
	}
	//10，流程状态改变时可能添加备注信息
	@Override
	public void UpdateBackinfo(int id,String backinfo){
		String sql="update task set backinfo=? where id=?;";
		DBUtil.update(sql, backinfo,id);
	}
	@Override
	public ArrayList<Task> findAssistTasks(int assistid, int offset, int rows) {
		String sql="select * from task where assistid=? and status in (1,2) limit ?,?;";
		return (ArrayList<Task>)DBUtil.query(sql,  new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
						 Task task=new Task();
						   task.setId(rs.getInt("id"));
						   task.setName(rs.getString("name"));
						   task.setContent(rs.getString("content"));
					       task.setDue(rs.getDate("due"));
						   task.setAssistid(rs.getString("assistid"));
						   task.setParentid(rs.getInt("parentid"));
						   task.setStartdate(rs.getDate("startdate"));
						   task.setStatus(Integer.valueOf(rs.getInt("status")));
							task.setBackinfo(rs.getString("backinfo"));

					       list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, assistid,offset,rows);
		
	}
	@Override
	public int findAssistTasks(int assistid) {
		String sql="select * from task where assistid=? and status in (1,2);";
		return (Integer)DBUtil.query(sql,  new ResultSetHanlder(){

			@Override
			public Object doHandler(ResultSet rs) {
				Integer total=0;
				try {
					while(rs.next()){
						total+=1;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return total;
			}
		},assistid);
	}*/
	//更新name字段
	@Override
	public void updateName(int id,String name) {
		String sql="update task set name=? where id=?;";
		DBUtil.update(sql,name,id);
	}
	@Override
	public void deleteTask(int id) {
		String sql="delete from task where id=?;";
		DBUtil.update(sql, id);
	}
	@Override
	public void updateResponsibility(int id, String responsibility) {
		String sql="update task set responsibility=? where id=?;";
		DBUtil.update(sql, responsibility,id);
	}
	@Override
	public void updateDue(int id, String due) {
		String sql="update task set due=? where id=?;";
		DBUtil.update(sql, due,id);
		
	}
	@Override
	public void addJoin(int id, String join) {
		String sql="insert into task_joins values(?,?);";
		DBUtil.update(sql, id,join);
		
	}
	@Override
	public void updateContent(int id, String content) {
		String sql="update task set content=? where id=?;";
		DBUtil.update(sql, content,id);
		
	}
	@Override
	public int addsubTask(Task task) {
		String sql="insert into task(name,content,due,assistid,status,responsibility,parentid) values(?,?,?,?,0,?,?);";
		String sql2="select last_insert_id();";
		return DBUtil.update2(sql,sql2,task.getName(),task.getContent(),task.getDue(),task.getAssistid(),task.getResponsibility(),task.getParentid());
		
	}
	@Override
	public ArrayList<Task> findSubTask(int parentid) {
		String sql="select * from task where parentid=? order by due desc ,id desc ;";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
						Task task=new Task();
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setAttachmenturl(rs.getString("attachmenturl"));
						task.setAssistid(rs.getString("assistid"));
						task.setDue(rs.getDate("due"));
						task.setResponsibility(rs.getString("responsibility"));
						task.setParentid(rs.getInt("parentid"));
						task.setStatus(rs.getInt("status"));
						list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, parentid);
	}
	@Override
	public Task findById(int id) {
		String sql="select * from task where id=?";
		return (Task)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				Task task=null;
				try {
					if(rs.next()){
						task=new Task();
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setAttachmenturl(rs.getString("attachmenturl"));
						task.setAssistid(rs.getString("assistid"));
						task.setDue(rs.getDate("due"));
						task.setResponsibility(rs.getString("responsibility"));
						task.setParentid(rs.getInt("parentid"));
						task.setStatus(rs.getInt("status"));
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return task;
			}
		}, id);
	}
	@Override
	public ArrayList<Assister> findJoinsById(int id) {
		String sql="select * from task_joins where taskid=?;";
		return (ArrayList<Assister>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Assister> list=new ArrayList<Assister>();
				try {
					while(rs.next()){
						Assister assist=new Assister();
						assist.setId(rs.getString("assistid"));
						list.add(assist);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, id);
	}
	@Override
	public void deleteJoin(String assistid) {
		String sql="delete from task_joins where assistid=?";
		DBUtil.update(sql, assistid);
	}
	@Override
	public ArrayList<User> findUserById(int taskid) {
		String sql="select * from user where uid in (select userid from task_user where taskid=?) and type>0;";
		return (ArrayList<User>)DBUtil.query(sql, new ResultSetHanlder() {
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<User> list=new ArrayList<User>();
				try {
					while(rs.next()){
						User user=new User();
						user.setUid(rs.getString("uid"));
						user.setPhone(rs.getString("phone"));
						user.setPassword(rs.getString("password"));
						user.setEmail(rs.getString("email"));
						user.setSchool(rs.getString("school"));
						user.setGrade(rs.getInt("grade"));
						user.setDue(rs.getDate("due").toString());
						user.setOpenid(rs.getString("openid"));
						user.setType(rs.getInt("type"));
						user.setAssistid(rs.getString("assistid"));
						user.setName(rs.getString("name"));
						list.add(user);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, taskid);
	}
	@Override
	public void deleteUserTask(int taskid,String uid) {
		String sql="delete from task_user where taskid=? and userid=?;";
	    DBUtil.update(sql,taskid,uid);
	}
	@Override
	public int addUser(int taskid, String uid) {
		String sql="insert into task_user values(?,?);";
		return DBUtil.update3(sql, taskid,uid);
	}
	@Override
	public void addAttachmentUrl(int taskid, String url,String title) {
		String sql="insert into task_attachmenturl(taskid,url,title) values(?,?,?);";
		DBUtil.update(sql, taskid,url,title);
		
	}
	@Override
	public ArrayList<String[]> findAttachment(int id) {
		String sql="select * from task_attachmenturl where taskid=?;";
		return (ArrayList<String[]>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<String[]> list=new ArrayList<String[]>();
				try {
					while(rs.next()){
						String[] str={String.valueOf(rs.getInt("id")),rs.getString("url"),rs.getString("title")};
						list.add(str);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, id);
	}
	@Override
	public void deleteAttachment(int id) {
		String sql="delete from task_attachmenturl where id=?;";
		DBUtil.update(sql, id);
		
	}
	@Override
	public void changeStatus(int status,int id) {
		String sql="update task set status=? where id=?;";
		DBUtil.update(sql, status,id);
	}
	@Override
	public int addTaskTravel(int taskid, int travelid) {
		String sql="insert into task_travel values(?,?);";
		return DBUtil.update3(sql, taskid,travelid);
		
	}
	@SuppressWarnings("unchecked")
	@Override
	public ArrayList<Travel> findTravelById(int taskid) {
		String sql="select * from travel where id in(select travelid from task_travel where taskid=?);";
		return (ArrayList<Travel>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Travel> list=new ArrayList<Travel>();
				try {
					while(rs.next()){
						Travel travel=new Travel();
						travel.setId(rs.getInt("id"));
						travel.setTitle(rs.getString("title"));
						travel.setContent(rs.getString("content"));
						travel.setMoney(rs.getDouble("money"));
						travel.setFirstimgurl(rs.getString("firstimgurl"));
						travel.setDatetime(rs.getDate("datetime"));
						list.add(travel);
						
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				
				return list;
			}
		}, taskid);
	}
	@Override
	public void deleteTravel(int taskid, int travelid) {
		String sql="delete from task_travel where taskid=? and travelid=?;";
		DBUtil.update(sql, taskid,travelid);
	}
	@Override	
	public ArrayList<Task> myCreate(String assistid) {
		String sql="select * from task where assistid=? order  by due desc ;";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder() {
					
					@Override
					public Object doHandler(ResultSet rs) {
						ArrayList<Task> list=new ArrayList<Task>();
						try {
							while(rs.next()){
								Task task=new Task();
								task.setId(rs.getInt("id"));
								task.setName(rs.getString("name"));
								task.setContent(rs.getString("content"));
								task.setDue(rs.getDate("due"));
								task.setAssistid(rs.getString("assistid"));
								task.setParentid(rs.getInt("parentid"));
								task.setStatus(Integer.valueOf(rs.getInt("status")));
								task.setResponsibility(rs.getString("responsibility"));
								task.setAttachmenturl(rs.getString("attachmenturl"));
								list.add(task);
								
							}
						} catch (SQLException e) {
							// TODO Auto-generated catch block
							e.printStackTrace();
							return null;
						}
						
						return list;
					}
				}, assistid);
	}
	@Override
	public ArrayList<Task> AllTaskFinish() {
		String sql="select * from task where status=1  order by due desc, id desc;";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder(){
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
						Task task=new Task();
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setDue(rs.getDate("due"));
						task.setAssistid(rs.getString("assistid"));
						task.setParentid(rs.getInt("parentid"));
						task.setStatus(Integer.valueOf(rs.getInt("status")));
						task.setResponsibility(rs.getString("responsibility"));
						task.setAttachmenturl(rs.getString("attachmenturl"));
						list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		},null);
	}
	@Override
	public ArrayList<Task> TaskFinishByAssistid(String assistid) {
		String sql="select * from task where (id in (select id from task where assistid=? or responsibility=?) or" 
                +" id in (select id from task where id in (select taskid from task_joins where assistid=?))) and status=1 ;";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
						Task task=new Task();
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setDue(rs.getDate("due"));
						task.setAssistid(rs.getString("assistid"));
						task.setParentid(rs.getInt("parentid"));
						task.setStatus(Integer.valueOf(rs.getInt("status")));
						task.setResponsibility(rs.getString("responsibility"));
						task.setAttachmenturl(rs.getString("attachmenturl"));
						list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, assistid,assistid,assistid);
	}
	@Override
	public ArrayList<Task> unfinishByAssitid(String assistid) {
		String sql="select * from task where (id in (select id from task where responsibility=?) or" 
                +" id in (select id from task where id in (select taskid from task_joins where assistid=?))) and status=0 order by due desc;";
		return (ArrayList<Task>)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				ArrayList<Task> list=new ArrayList<Task>();
				try {
					while(rs.next()){
						Task task=new Task();
						task.setId(rs.getInt("id"));
						task.setName(rs.getString("name"));
						task.setContent(rs.getString("content"));
						task.setDue(rs.getDate("due"));
						task.setAssistid(rs.getString("assistid"));
						task.setParentid(rs.getInt("parentid"));
						task.setStatus(Integer.valueOf(rs.getInt("status")));
						task.setResponsibility(rs.getString("responsibility"));
						task.setAttachmenturl(rs.getString("attachmenturl"));
						list.add(task);
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return list;
			}
		}, assistid,assistid);
	}
	@Override
	public int unfinishCountByAssitid(String assistid) {
		String sql="select * from task where (id in (select id from task where responsibility=?) or" 
                +" id in (select id from task where id in (select taskid from task_joins where assistid=?))) and status=0 ;";
		return (Integer)DBUtil.query(sql, new ResultSetHanlder() {
			
			@Override
			public Object doHandler(ResultSet rs) {
				Integer count=0;
				try {
					
					while(rs.next()){
						count+=1;
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return null;
				}
				return count;
			}
		}, assistid,assistid);
	}

}
